import time
import uuid

import pymysql
import requests
from lxml import etree


class LiuheCaiSpider:
    def __init__(self):
        self.headers = {
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
        }

    def get_html(self, url):
        max_retries = 5
        for attempt in range(max_retries):
            try:
                response = requests.get(url, headers=self.headers, timeout=10)
                response.encoding = 'utf-8'
                return response.text
            except Exception as e:
                if attempt < max_retries - 1:  # 前4次失败才重试
                    print(f"请求失败，第{attempt+1}次重试: {e}")
                    time.sleep(2)  # 添加2秒延迟避免频繁请求
                else:
                    print(f"请求失败，已达最大重试次数: {e}")
        return None

    def parse_data(self, html,xPath):
        if not html:
            return []

        dom = etree.HTML(html)
        # 示例XPath，根据实际网页结构调整
        results = dom.xpath(xPath)
        return [
            item.strip()
            .replace('\xa0', '')  # 移除HTML非换行空格
            .replace(' , ', ',')  # 清理逗号周围的空格
            for item in results if item.strip()
        ]

    def getLHCData(self):
        # 使用示例
        spider = LiuheCaiSpider()
        structured = []
        j=1
        while j <= 1:
            url = f"https://www.pilio.idv.tw/ltohk/listbbk.asp?indexpage={j}&orderby=new"
            html = spider.get_html(url)
            data = spider.parse_data(html, '//tr/td/font/b/text() | //tr/td/b/text()')
            # 新增数据结构化处理
            cleaned = [item for item in data if item.strip()]
            i = 0
            while i < len(cleaned):
                try:
                    # 识别记录开始模式：数字开头的期号
                    if cleaned[i].isdigit() and '/' in cleaned[i + 1]:
                        record = {
                            '期号': int(cleaned[i]),
                            '日期': cleaned[i + 1],
                            '普通号码': cleaned[i + 2].split(','),
                            '特别号码': cleaned[i + 3] if i + 3 < len(cleaned) and cleaned[i + 3].isdigit() else None
                        }
                        structured.append(record)
                        i += 4 if record['特别号码'] else 3
                    else:
                        i += 1
                except IndexError:
                    break
            print("解析结果:", structured)
            j = j+1
            time.sleep(0.5)
        return structured

    def insert_records(self):
        structured=self.getLHCData()
        # ... 数据库插入逻辑 ...
        # 存入数据库
        # ... 数据库插入逻辑 ...
        # 独立数据库配置（根据实际需要修改以下参数）
        db_config = {
            'host': '175.178.5.14',
            'port': 33066,
            'user': 'root',
            'password': '07M@Y0sqL1',
            'database': 'stock',
            'charset': 'utf8mb4',
            'connect_timeout': 10
        }
        try:

            conn = pymysql.connect(**db_config)
            #获取所有期号
            sql = """
                           SELECT issue FROM lhc_history_data
                           """
            # 执行查询
            with conn.cursor() as cursor:
                cursor.execute(sql)
                existing_issues = {row[0] for row in cursor.fetchall()}
                # 过滤出不存在的期号
                structured = [record for record in structured if record['期号'] not in existing_issues]
                # 打印过滤结果
                print("需要插入的期号:", structured)


            with conn.cursor() as cursor:

                sql = """
                           INSERT INTO lhc_history_data
                           (id, issue, date, number_first, number_second, number_third, 
                            number_four, number_five, number_six, number_special)
                           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                           """
                # 生成UUID并构建参数
                params = [(
                    str(uuid.uuid4()),  # 生成UUID
                    record['期号'],
                    record['日期'],
                    record['普通号码'][0],  # 解包6个普通号码
                    record['普通号码'][1],
                    record['普通号码'][2],
                    record['普通号码'][3],
                    record['普通号码'][4],
                    record['普通号码'][5],
                    record['特别号码']
                ) for record in structured]

                cursor.executemany(sql, params)

                conn.commit()
                print(f"成功插入 {cursor.rowcount} 条记录")

        except Exception as e:
            print(f"数据库插入失败: {e}")
            conn.rollback()

if __name__ == "__main__":
    spider = LiuheCaiSpider()
    spider.insert_records()
